-- Given the HierarchyKey of the Hierarchy element near which the new (or moved) one needs to go, and where, in relation
-- to this near one it needs to be, this procedure returns a SortOrder that will place the new (or moved) record
-- in the right place.  If there is no space to put it there (i.e., there is no integer between the record just prior
-- and the record just after) the entire hierarchy, from the root, will be resorted so all SortOrders are divisible by 2048
-- Values for addType parameter:
--    0 - Before the adjacent Hierarchy record, as a sibling
--    1 - After the adjacent Hierarchy record, as a sibling
--    2 - As the first child of the adjacent Hierarchy record
--    3 - As the last child of the adjacent Hierarchy record
--    4 - With the same sort order as that of the adjacent Hierarchy record (for use when sorting after the fact, e.g., alpha)
-- TO DO: implement locking later so sort order can't come up twice with highly concurrent work
-- TO DO: Resort has deadlocking issues - fix!
CREATE PROCEDURE [dbo].[asi_HierarchyGetAdjacentSortOut] @adjacentHierarchyKey uniqueidentifier, @addType smallint = 3, @NewSort int OUTPUT AS
DECLARE
      @LowSort int, @HighSort int, @SortOrder int, @RootHierarchyKey uniqueidentifier, @Run bit
BEGIN
      SET @Run = 1
      WHILE @Run = 1
      BEGIN
            -- BeforeAsSibling - SortOrder is between that of the adjacentHierarchyKey and the max SortOrder lower than that (or 0 if none)
            IF @addType = 0
            BEGIN
                  SELECT @RootHierarchyKey = a.RootHierarchyKey, @HighSort = a.SortOrder, @LowSort = ISNULL(Max(b.SortOrder),0)
                   FROM Hierarchy a LEFT OUTER JOIN Hierarchy b
                          ON a.RootHierarchyKey = b.RootHierarchyKey
                         AND a.SortOrder > b.SortOrder
                  WHERE a.HierarchyKey = @adjacentHierarchyKey
                  GROUP BY a.SortOrder, a.RootHierarchyKey
            END
      
            -- AfterAsSibling or AsLastChild - The SortOrder for both is the same but parent and depth will be different.
            -- SortOrder is between the min SortOrder on an element with a higher SortOrder and an equal or higher depth
            -- (lower depth number) than that of the adjacentHierarchyKey and the max sort order lower than that.
            ELSE IF @addType = 1 OR @addType = 3
            BEGIN
                  -- first, find the high (if it is there) as the lowest sort order that is higher than the adjacent one
                  -- and with a depth equal or shallower than the adjacent one
                  SELECT @RootHierarchyKey = a.RootHierarchyKey, @HighSort = Min(b.SortOrder)
                   FROM Hierarchy a LEFT OUTER JOIN Hierarchy b
                          ON a.RootHierarchyKey = b.RootHierarchyKey
                         AND a.SortOrder < b.SortOrder
                     AND a.Depth >= b.Depth
                  WHERE a.HierarchyKey = @adjacentHierarchyKey
                  GROUP BY a.RootHierarchyKey
      
                  -- if we found the high sort, then the low is the one just lower
                  IF @HighSort IS NOT NULL
                  BEGIN
                        SELECT @LowSort = Max(a.SortOrder)
                         FROM Hierarchy a
                        WHERE a.RootHierarchyKey = @RootHierarchyKey
                           AND a.SortOrder < @HighSort
                  END
                  -- if we didn't find one, the Low is the last sort order for the root and the max is that + 4096
                  ELSE
                  BEGIN
                        SELECT @LowSort = Max(a.SortOrder)
                         FROM Hierarchy a
                        WHERE a.RootHierarchyKey = @RootHierarchyKey
      
                        SET @HighSort = @LowSort + 4096
                  END
      
            END
      
            -- AsFirstChild  - SortOrder is between that of the adjacentHierarchyKey and the min SortOrder higher than that
            ELSE IF @addType = 2
            BEGIN
                  SELECT @RootHierarchyKey = a.RootHierarchyKey, @LowSort = a.SortOrder, @HighSort = ISNULL(Min(b.SortOrder), a.SortOrder + 4096)
                   FROM Hierarchy a LEFT OUTER JOIN Hierarchy b
                          ON a.RootHierarchyKey = b.RootHierarchyKey
                     AND a.SortOrder < b.SortOrder
                  WHERE a.HierarchyKey = @adjacentHierarchyKey
                  GROUP BY a.SortOrder, a.RootHierarchyKey
            END
            -- Same  - SortOrder is the same as the adjecent one (presumably the parent or a sibling)
            ELSE IF @addType = 4
            BEGIN
                  SELECT @RootHierarchyKey = a.RootHierarchyKey, @LowSort = a.SortOrder, @HighSort = a.SortOrder
                   FROM Hierarchy a
                  WHERE a.HierarchyKey = @adjacentHierarchyKey
            END
      
            -- if there is no room between the low and the high, need to redo the sort order for this root
            IF @HighSort - @LowSort < 2  AND @addType != 4
                  EXEC asi_HierarchyResort2 @RootHierarchyKey
            ELSE
                  SET @Run = 0
      END

      SET @NewSort = ISNULL(((@HighSort - @LowSort) / 2) + @LowSort, 0)
END


GO